package dao;


import com.alibaba.fastjson.JSON;
import domin.PersonalBorrowingInformation;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

@Slf4j
public final class PersonalBorrowingInformationDao {
    private static PersonalBorrowingInformationDao
            personalborrowinginformationDao = new PersonalBorrowingInformationDao();

    PreparedStatement preparedStatement = null;

    ResultSet resultSet = null;

    private PersonalBorrowingInformationDao() {

    }

    public static PersonalBorrowingInformationDao getInstance() {
        return personalborrowinginformationDao;
    }

    public Collection<PersonalBorrowingInformation> findAll
    (Connection connection, Collection<Condition> condition,
            Pagination pagination) throws SQLException {

        Collection<PersonalBorrowingInformation> personalborrowinginformations = new TreeSet<PersonalBorrowingInformation>();
        int totalNum = CommonDao.getInstance().count(connection,
                "Personalborrowinginformation");

        String sql_select_all = "SELECT * FROM personalborrowinginformation ";
        if (condition != null){
            sql_select_all += Condition.toWhereClause(JSON.toJSONString(condition));
            log.trace(sql_select_all);
        }
        if (pagination != null){
            sql_select_all += pagination.toLimitClause(totalNum);
            log.trace(sql_select_all);
        }
        this.preparedStatement = connection.prepareStatement(sql_select_all);
        this.resultSet = preparedStatement.executeQuery();
        while (this.resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String borrowingBook = resultSet.getString("borrowingBook");
            int remainingBorrowingTime = resultSet.getInt("remainingBorrowingTime");
            String tips = resultSet.getString("tips");


            PersonalBorrowingInformation  foundPersonalBorrowingInformation= new PersonalBorrowingInformation(id,name,
                    borrowingBook,remainingBorrowingTime,tips);
            personalborrowinginformations.add(foundPersonalBorrowingInformation);

        }
        JdbcHelper.close(this.resultSet,this.preparedStatement);
        return personalborrowinginformations;
    }


    public PersonalBorrowingInformation find(Connection connection,
                                             Integer id) throws SQLException {
        PersonalBorrowingInformation foundPersonalBorrowingInformation =
                new PersonalBorrowingInformation();

        this.preparedStatement = connection.prepareStatement("select * from PersonalBorrowingInformation where id=" + id);

        this.resultSet = this.preparedStatement.executeQuery();


        this.resultSet.next();
        foundPersonalBorrowingInformation.setId(this.resultSet.getInt("id"));
        foundPersonalBorrowingInformation.setName(this.resultSet.getString("name"));
        foundPersonalBorrowingInformation.setBorrowingBook(this.resultSet.getString("borrowingBook"));
        foundPersonalBorrowingInformation.setRemainingBorrowingTime(this.resultSet.getInt("remainingBorrowingTime"));
        foundPersonalBorrowingInformation.setTips(this.resultSet.getString("tips"));
        JdbcHelper.close(resultSet,preparedStatement);
        return foundPersonalBorrowingInformation;
    }
    public Collection<PersonalBorrowingInformation> getAllWithCondition
            (Connection connection, String s) throws SQLException{
        Collection<PersonalBorrowingInformation> personalBorrowingInformations
                = new HashSet<PersonalBorrowingInformation>();
        String value = "%" + s + "%";
        value=" \"" + value + "\"";
        PreparedStatement preparedStatement = connection.prepareStatement("" +
                "select * from personalborrowinginformation where " +
                "name like " + value
                +" OR borrowingBook like " + value
                +" OR  tips like "+value);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            PersonalBorrowingInformation personalborrowinginformation = null;
            Integer id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String borrowingBook  = resultSet.getString("borrowingBook");
            Integer remainingBorrowingTime = resultSet.getInt("remainingBorrowingTime");
            String tips = resultSet.getString("tips");
            personalborrowinginformation = new PersonalBorrowingInformation(id,name,borrowingBook,remainingBorrowingTime,tips);
            personalBorrowingInformations.add(personalborrowinginformation);
        }
        JdbcHelper.close(null,preparedStatement);
        return personalBorrowingInformations;
    }
    public boolean update(Connection connection,
                          PersonalBorrowingInformation personalBorrowingInformation) throws SQLException {
        this.preparedStatement = connection.prepareStatement(
                "update PersonalBorrowingInformation" +
                " set name=?," +
                "borrowingBook =?,remainingBorrowingTime =?," +
                "tips=? where id=?");

        this.preparedStatement.setString(1,personalBorrowingInformation.getName());
        this.preparedStatement.setString(2,personalBorrowingInformation.getBorrowingBook());
        this.preparedStatement.setInt(3,personalBorrowingInformation.getRemainingBorrowingTime());
        this.preparedStatement.setString(4,personalBorrowingInformation.getTips());
        this.preparedStatement.setInt(5,personalBorrowingInformation.getId());

        int rowAffected = this.preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);

        return rowAffected > 0;
    }


    public boolean add(Connection connection, PersonalBorrowingInformation personalBorrowingInformation) throws SQLException {
        this.preparedStatement = connection.prepareStatement("INSERT INTO PersonalBorrowingInformation (name,borrowingBook,remainingBorrowingTime, tips ) VALUES (?,?,?,?)");
        preparedStatement.setString(1,personalBorrowingInformation.getName());
        preparedStatement.setString(2,personalBorrowingInformation.getBorrowingBook());
        preparedStatement.setInt(3,personalBorrowingInformation.getRemainingBorrowingTime());
        preparedStatement.setString(4,personalBorrowingInformation.getTips());

        int rowAffected = this.preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);

        return rowAffected>0;
    }

    public boolean delete(Connection connection,Integer id) throws SQLException {
        this.preparedStatement = connection.prepareStatement("delete from PersonalBorrowingInformation where id=" + id);

        int rowAffected = preparedStatement.executeUpdate();

        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

}
